/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package database;

import java.sql.*;

/**
 *
 * @author Andi
 */
public class Database {

    private String DB_HOST = "localhost";
    private String DB_USER = "root";
    private String DB_PASSWORD = "";
    private String DB_NAME = "progin4";
    private Connection connection;

    public Database(){
        this.connect();
    }

    public String connect() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String connectionUrl = "jdbc:mysql://" + this.DB_HOST + "/" + this.DB_NAME + "?user=" + this.DB_USER + "&password=" + this.DB_PASSWORD;
            this.connection = DriverManager.getConnection(connectionUrl);
            return "berhasil";
        } catch (SQLException e) {
            System.out.println("SQL Exception:" + e.toString());
            return "shit2";
        } catch (ClassNotFoundException cE) {
            System.out.println("Class NOt Found Exception: " + cE.toString());
            return "shit";
        }
    }

    /*
     * Menambahkan user dalam database
     * @throws SQLException
     */
    public void addUser (String userName, String password, String fullName, String email) throws SQLException
    {
        Statement statement = null;
        String query = "INSERT INTO user(user_name,password,full_name,email,max_rooms) VALUES('" + userName + "','" + password + "','" + fullName + "','" + email + "','10')";
        statement = connection.createStatement();
        statement.executeUpdate(query);
    }

    /*
     * Menampilkan informasi user dari database
     * @throws SQLException
     * @return ResultSet
     */
    public ResultSet getInfoUser (String userName) throws SQLException
    {
        Statement statement = null;
        String query = "SELECT * FROM user WHERE user_name = '" + userName + "'";
        statement = connection.createStatement();
        ResultSet rs = statement.executeQuery(query);
        rs.beforeFirst();
        rs.next();
        return rs;
    }

    /*
     * Menampilkan informasi user dari database
     * @throws SQLException
     * @return ResultSet
     */
    public boolean isUserExist (String userName) throws SQLException
    {
        Statement statement = null;
        String query = "SELECT * FROM user WHERE user_name = '" + userName + "'";
        statement = connection.createStatement();
        ResultSet rs = statement.executeQuery(query);
        rs.beforeFirst();
        return rs.next();
    }

    /*
     * Menampilkan informasi user id dari database
     * @throws SQLException
     * @return ResultSet
     */
    public int getUserID (String userName) throws SQLException
    {
        Statement statement = null;
        String query = "SELECT * FROM user WHERE user_name = '" + userName + "'";
        statement = connection.createStatement();
        ResultSet rs = statement.executeQuery(query);
        rs.beforeFirst();
        rs.next();
        int result = rs.getInt("user_id");
        return result;
    }

    /*
     * Menampilkan informasi password user dari database
     * @throws SQLException
     * @return ResultSet
     */
    public String getPasswordUser (String userName) throws SQLException
    {
        Statement statement = null;
        String query = "SELECT * FROM user WHERE user_name = '" + userName + "'";
        statement = connection.createStatement();
        ResultSet rs = statement.executeQuery(query);
        rs.beforeFirst();
        rs.next();
        String result = rs.getString("password");
        return result;
    }

    /*
     * Menampilkan informasi full name user dari database
     * @throws SQLException
     * @return ResultSet
     */
    public String getFullNameUser (String userName) throws SQLException
    {
        Statement statement = null;
        String query = "SELECT * FROM user WHERE user_name = '" + userName + "'";
        statement = connection.createStatement();
        ResultSet rs = statement.executeQuery(query);
        rs.beforeFirst();
        rs.next();
        String result = rs.getString("full_name");
        return result;
    }

    /*
     * Menampilkan informasi email user dari database
     * @throws SQLException
     * @return ResultSet
     */
    public String getEmailUser (String userName) throws SQLException
    {
        Statement statement = null;
        String query = "SELECT * FROM user WHERE user_name = '" + userName + "'";
        statement = connection.createStatement();
        ResultSet rs = statement.executeQuery(query);
        rs.beforeFirst();
        rs.next();
        String result = rs.getString("email");
        return result;
    }

    /*
     * Menampilkan informasi role user dari database
     * @throws SQLException
     * @return ResultSet
     */
    public String getRoleUser (String userName) throws SQLException
    {
        Statement statement = null;
        String query = "SELECT * FROM user WHERE user_name = '" + userName + "'";
        statement = connection.createStatement();
        ResultSet rs = statement.executeQuery(query);
        rs.beforeFirst();
        rs.next();
        String result = rs.getString("role");
        return result;
    }

    /*
     * Mengecek apakah user sedang diband
     * @throws SQLException
     * @return boolean
     */
    public boolean isBanUser (String userName) throws SQLException
    {
        Statement statement = null;
        String query = "SELECT * FROM user WHERE user_name = '" + userName + "'";
        statement = connection.createStatement();
        ResultSet rs = statement.executeQuery(query);
        rs.beforeFirst();
        rs.next();
        String result = rs.getString("ban");
        if (result.equalsIgnoreCase("yes")){
            return true;
        }else{
            return false;
        }
    }

    /*
     * Mengecek apakah user sebagai role moderator
     * @throws SQLException
     * @return boolean
     */
    public boolean isModerator (String userName) throws SQLException
    {
        Statement statement = null;
        String query = "SELECT * FROM user WHERE user_name = '" + userName + "'";
        statement = connection.createStatement();
        ResultSet rs = statement.executeQuery(query);
        rs.beforeFirst();
        rs.next();
        String result = rs.getString("role");
        if (result.equalsIgnoreCase("moderator")){
            return true;
        }else{
            return false;
        }
    }

    /*
     * Mengecek apakah user sebagai role admin
     * @throws SQLException
     * @return boolean
     */
    public boolean isAdmin (String userName) throws SQLException
    {
        Statement statement = null;
        String query = "SELECT * FROM user WHERE user_name = '" + userName + "'";
        statement = connection.createStatement();
        ResultSet rs = statement.executeQuery(query);
        rs.beforeFirst();
        rs.next();
        String result = rs.getString("role");
        if (result.equalsIgnoreCase("admin")){
            return true;
        }else{
            return false;
        }
    }

    /*
     * melakukan ban pada user
     * @throws SQLException
     */
    public void banUser (String userName) throws SQLException
    {
        Statement statement = null;
        String query = "UPDATE user SET ban ='yes' WHERE user_name = '" + userName + "'";
        statement = connection.createStatement();
        statement.executeUpdate(query);
    }

    /*
     * melakukan unban pada user
     * @throws SQLException
     */
    public void unBanUser (String userName) throws SQLException
    {
        Statement statement = null;
        String query = "UPDATE user SET ban ='no' WHERE user_name = '" + userName + "'";
        statement = connection.createStatement();
        statement.executeUpdate(query);
    }

    /*
     * Menampilkan informasi max_rooms user dari database
     * @throws SQLException
     * @return ResultSet
     */
    public int getMaxRooms (String userName) throws SQLException
    {
        Statement statement = null;
        String query = "SELECT * FROM user WHERE user_name = '" + userName + "'";
        statement = connection.createStatement();
        ResultSet rs = statement.executeQuery(query);
        rs.beforeFirst();
        rs.next();
        int result = rs.getInt("max_rooms");
        return result;
    }

    /*
     * melakukan ban pada user
     * @throws SQLException
     */
    public void setUserMaxRooms (String userName,int numRooms) throws SQLException
    {
        Statement statement = null;
        String query = "UPDATE user SET max_rooms ='" + numRooms + "' WHERE user_name = '" + userName + "'";
        statement = connection.createStatement();
        statement.executeUpdate(query);
    }

    /*
     * Menampilkan apakah di suatu kategori sudah terdapat moderatornya
     * @throws SQLException
     * @return ResultSet
     */
    public boolean isParentKategori (String categoryName) throws SQLException
    {
        Statement statement = null;
        String query = "SELECT * FROM kategori WHERE kategori_name = '" + categoryName + "'";
        statement = connection.createStatement();
        ResultSet rs = statement.executeQuery(query);
        rs.beforeFirst();
        rs.next();
        if(rs.getString("parent_name").equalsIgnoreCase("NULL")){
            return true;
        }else{
            return false;
        }
    }

    /*
     * Menampilkan semua kategori yang menjadi parent
     * @throws SQLException
     * @return ResultSet
     */
    public ResultSet showAllParentKategori () throws SQLException
    {
        Statement statement = null;
        String query = "SELECT * FROM kategori WHERE parent_name = 'NULL'";
        statement = connection.createStatement();
        ResultSet rs = statement.executeQuery(query);
        rs.beforeFirst();
        rs.next();
        return rs;
    }

    /*
     * Menampilkan semua kategori yang menjadi parent
     * @throws SQLException
     * @return ResultSet
     */
    public ResultSet showAllSubKategori (String parentName) throws SQLException
    {
        Statement statement = null;
        String query = "SELECT * FROM kategori WHERE parent_name = '" + parentName+ "'";
        statement = connection.createStatement();
        ResultSet rs = statement.executeQuery(query);
        rs.beforeFirst();
        rs.next();
        return rs;
    }

    /*
     * Menambahkan kategori dalam database
     * @throws SQLException
     */
    public void addKategori (String kategoriName) throws SQLException
    {
        Statement statement = null;
        String query = "INSERT INTO kategori(kategori_name,parent_name,moderator) VALUES('" + kategoriName + "','NULL','NULL')";
        statement = connection.createStatement();
        statement.executeUpdate(query);
    }

    /*
     * Menambahkan subkategori dalam suatu kategori
     * @throws SQLException
     */
    public void addSubKategori (String subKategoriName, String kategoriName) throws SQLException
    {
        Statement statement = null;
        String query = "INSERT INTO kategori(kategori_name,parent_name,moderator) VALUES('" + subKategoriName + "','" + kategoriName+ "','NULL')";
        statement = connection.createStatement();
        statement.executeUpdate(query);
    }

    /*
     * edit kategori dalam database
     * @throws SQLException
     */
    public void editKategori (String kategoriName, String newName) throws SQLException
    {
        Statement statement = null;
        String query = "SELECT * FROM kategori WHERE parent_name = '" + kategoriName + "'";
        statement = connection.createStatement();
        ResultSet rs = statement.executeQuery(query);
        rs.beforeFirst();
        if (rs.next()){
            String query2 = "UPDATE kategori SET parent_name ='" + newName + "' WHERE parent_name = '" + kategoriName + "'";
            statement = connection.createStatement();
            statement.executeUpdate(query2);
        }
        String query3 = "UPDATE kategori SET kategori_name ='" + newName + "' WHERE kategori_name = '" + kategoriName + "'";
        statement = connection.createStatement();
        statement.executeUpdate(query3);
    }

    /*
     * delete kategori dalam database
     * @throws SQLException
     */
    public void deleteKategori (String kategoriName) throws SQLException
    {
        Statement statement = null;
        String query = "SELECT * FROM kategori WHERE parent_name = '" + kategoriName + "'";
        statement = connection.createStatement();
        ResultSet rs = statement.executeQuery(query);
        rs.beforeFirst();
        if (rs.next()){
            String query2 = "DROP TABLE kategori WHERE parent_name = '" + kategoriName + "'";
            statement = connection.createStatement();
            statement.executeUpdate(query2);
        }
        String query3 = "DROP TABLE kategori WHERE kategori_name = '" + kategoriName + "'";
        statement = connection.createStatement();
        statement.executeUpdate(query3);
    }

    /*
     * Menampilkan apakah di suatu kategori sudah terdapat moderatornya
     * @throws SQLException
     * @return ResultSet
     */
    public boolean isSetModerator (String categoryName) throws SQLException
    {
        Statement statement = null;
        String query = "SELECT * FROM kategori WHERE kategori_name = '" + categoryName + "'";
        statement = connection.createStatement();
        ResultSet rs = statement.executeQuery(query);
        rs.beforeFirst();
        if(rs.next()){
            return true;
        }else{
            return false;
        }
    }

    /*
     * melakukan set user sebagai moderator
     * @throws SQLException
     */
    public void addModerator (String userName, String categoryName) throws SQLException
    {
        Statement statement = null;
        String query0 = "UPDATE kategori SET moderator = '" + userName + "' WHERE parent_name = '" + categoryName + "'";
        statement = connection.createStatement();
        statement.executeUpdate(query0);
        String query = "UPDATE user SET role ='moderator' WHERE user_name = '" + userName + "'";
        statement = connection.createStatement();
        statement.executeUpdate(query);
        String query2 = "UPDATE kategori SET moderator = '" + userName + "' WHERE kategori_name = '" + categoryName + "'";
        statement = connection.createStatement();
        statement.executeUpdate(query2);
    }

    /*
     * melakukan set user sebagai moderator
     * @throws SQLException
     */
    public void deleteModerator (String userName) throws SQLException
    {
        Statement statement = null;
        String query = "UPDATE user SET role ='user' WHERE user_name = '" + userName + "'";
        statement = connection.createStatement();
        statement.executeUpdate(query);
        String query2 = "UPDATE kategori SET moderator = 'NULL' WHERE moderator = '" + userName + "'";
        statement = connection.createStatement();
        statement.executeUpdate(query2);
    }

    /*
     * melakukan set user sebagai moderator
     * @throws SQLException
     */
    public void editModerator (String userName, String newCategoryName) throws SQLException
    {
        this.deleteModerator(userName);
        this.addModerator(userName, newCategoryName);
    }

    /*
     * Menambahkan friend dalam database
     * @throws SQLException
     */
    public void addFriend (String userName, String userNameFriend) throws SQLException
    {
        Statement statement = null;
        String query = "INSERT INTO friend(user_name,user_name_friend) VALUES('" + userName + "','" + userNameFriend + "')";
        statement = connection.createStatement();
        statement.executeUpdate(query);
    }

    /*
     * menampilkan daftar friend
     * @throws SQLException
     * @return ResultSet
     */
    public ResultSet showFriend (String userName) throws SQLException
    {
        Statement statement = null;
        String query = "SELECT * FROM friend WHERE user_name = '" + userName + "'";
        statement = connection.createStatement();
        ResultSet rs = statement.executeQuery(query);
        return rs;
    }



}
